﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;


namespace CM_Classes
{
    public class GroupAttrib
    {
        Group group = new Group();
        Attrib attrib = new Attrib();

        string connectionString = @"Data Source=t-sql;Initial Catalog=ConfigurationManager;Integrated Security=True";

        private int groupID;
        private int attribID;
        private String groupName;
        private String attribName;
        private bool grpAttrbDisabled;
        private string values;

        public int GroupID
        {
            get { return groupID; }
            set { groupID = value; }
        }        

        public int AttribID 
        {
            get { return attribID; }
            set { attribID = value; }
        }

        public String GroupName
        {
            get { return groupName; }
            set { groupName = value; }
        }

        public String AttribName
        {
            get { return attribName; }
            set { attribName = value; }
        }
        public string Values
        {
            get { return values; }
            set { values = value; }
        }        

        public bool GrpAttrbDisabled
        {
            get { return grpAttrbDisabled; }
            set { grpAttrbDisabled = value; }
        }

        //***************** end of properties

        public string AssignAttribToGroups(Attrib attribName, Group Groups) //to assign an attribute to one or more groups
        {
            string result = string.Empty;
          

            SqlConnection connection = new SqlConnection(connectionString);
            {
                try 
                {
                    using (SqlCommand insertCommand = new SqlCommand(
                    "INSERT INTO dbo.GroupAttrib VALUES(@GroupID, @AttributeID,  @GrpAttrbDisabled) ", connection))
                    {
                        insertCommand.Parameters.AddWithValue("@GroupID", groupID);
                        insertCommand.Parameters.AddWithValue("@AttributeID", attribID);
                        insertCommand.Parameters.AddWithValue("@GrpAttrbDisabled", grpAttrbDisabled);
                        

                        insertCommand.Connection = connection;
                        connection.Open();
                        insertCommand.ExecuteNonQuery();

                        result = " Success! ";
                    }
                    
                }  
                catch (System.Data.SqlClient.SqlException sqlException)
                {
                    result = sqlException.Message;
                }
                catch (Exception e)
                {
                    result = "There was an error assigning the attribute to the group. " + e.Message;
                }
            }

            return result;
         }
           

        public string AssignValueToGroup(string Value, Group Groups) // to assign a value to each group
        {
            string result = string.Empty;
            

            SqlConnection connection = new SqlConnection(connectionString);
            {
                try 
                {
                    using (SqlCommand insertCommand = new SqlCommand(
                    "INSERT INTO dbo.GroupAttrib VALUES(@GroupID, @Value, @GrpAttrbDisabled) ", connection))
                    {
                        insertCommand.Parameters.AddWithValue("@GroupID", groupID);
                        insertCommand.Parameters.AddWithValue("@Value", attribID);
                        insertCommand.Parameters.AddWithValue("@GrpAttrbDisabled", grpAttrbDisabled);
                        

                        insertCommand.Connection = connection;
                        connection.Open();
                        insertCommand.ExecuteNonQuery();

                        result = " Success! ";
                    }
                    
                }  
                catch (System.Data.SqlClient.SqlException sqlException)
                {
                    result = sqlException.Message;
                }
                catch (Exception e)
                {
                    result = "There was an error assigning the a value to the group. " + e.Message;
                }
            }

            return result;
         }


        public List<GroupAttrib> GetGroupAttribs()
        {
            List<GroupAttrib> GroupAttribList = new List<GroupAttrib>();
           // GroupAttrib groupAttrib = new GroupAttrib();

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                string queryString = "SELECT g.groupName as [GroupName], a.attribName as [AttributeName], ga.[Values], ga.grpAttribDisabled FROM [dbo].[GroupAttrib] as ga, [dbo].[Group] as g, [dbo].[Attrib] as a where ga.AttribID = a.AttribID and ga.GroupID = g.GroupID";
                try
                {
                    
                    connection.Open();

                    SqlCommand command = new SqlCommand(queryString, connection);

                    SqlDataReader reader = command.ExecuteReader();

                    while (reader.Read())
                    {
                        GroupAttrib aGroupAttrib = new GroupAttrib();
                        Group group = new Group();
                        Attrib attrib = new Attrib();

                        aGroupAttrib.GroupName = reader.GetString(0);
                        aGroupAttrib.AttribName = reader.GetString(1);
                        aGroupAttrib.Values = reader.GetString(2);
                        aGroupAttrib.GrpAttrbDisabled = reader.GetBoolean(3);
                        

                        GroupAttribList.Add(aGroupAttrib);
                    }

                    

                }

                catch (Exception e)
                {
                    Console.WriteLine(e.ToString());
                }
                return GroupAttribList;
            }

           
         }
           

        public string UpdateRecords()
        {
            string result = string.Empty;


            SqlConnection connection = new SqlConnection(connectionString);
            {
                try
                {
                    using (SqlCommand Command = new SqlCommand(
                    "UPDATE dbo.GroupAttrib SET GroupID=@groupID, AttribID=@attribID, GrpAttrbDisabled=@GrpAttrbDisabled, Value=@Value  WHERE attribID=@attribID", connection))
                    {
                        Command.Parameters.AddWithValue("@GroupID", groupID);
                        Command.Parameters.AddWithValue("@AttributeID", attribID);
                        Command.Parameters.AddWithValue("@GrpAttrbDisabled", grpAttrbDisabled);
                        Command.Parameters.AddWithValue("@Value", values);

                        Command.Connection = connection;
                        connection.Open();
                        Command.ExecuteNonQuery();

                        result = " Success! ";
                    }

                }
                catch (System.Data.SqlClient.SqlException sqlException)
                {
                    result = sqlException.Message;
                }
                catch (Exception e)
                {
                    result = "There was an error updating the record. " + e.Message;
                }
            }

            return result;
        }


        /* we have similar method returning a list. So why do we write a void method again. Comment by Amha
        
           public void DisplayAttributesOfGroups()
           {
               GroupAttrib ga = new GroupAttrib();
               ga.GetGroupAttribs();
           }

        
        // Why do we need to display the attribute. Is it not possible to get the record from the attrib class? comment by Amha
           public void DisplayAttributes()
           {
               Attrib a = new Attrib(); //use the GetAttribs() method to allow you to display all attriubtes on UI
               a.DisplayAttributes();
           }
         
        //I need clarification for the need to write the add row method. Comment by Amha
           public string AddRows() // to allow the user to add rows in the database
           {
               string result = string.Empty;
               return result;
           }
       */
        
    }
}
